﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using KongMing.ORM.SqlSugar.Models;
using KongMing.ORM.SqlSugar.QueryModels;
using SqlSugar;
using static KongMing.ORM.SqlSugar.SqlSugarHelper;

namespace KongMing.ORM.SqlSugar
{
    public class 基础查询
    {

        /// <summary>
        /// 基础列表查询
        /// </summary>
        public static void NormalQueryList()
        {
            using (var db = SqlSugarHelper.GetDB())
            {
                //1、查询所有
                var query_all = db.Queryable<Book>().ToList();

                //2、查询单条
                var query_first = db.Queryable<Book>().First(x => x.Sn == 1);

                //3、SqlServer 里面的withnolock
                var query_allNolock = db.Queryable<Book>().With(SqlWith.NoLock).ToList();

                var query_ins = db.Queryable<Book>().In(new string[] { "26d9bae8be154191a29599615358e912" }).ToList();

                //4、根据主键查询单条
                var query_primaryKey = db.Queryable<Book>().Select(x => x.Author).InSingle("26d9bae8be154191a29599615358e912");

                //7、* 查询实体 AS 数据实体 建立联系,查询需要的字段 *
                var query_as = db.Queryable<QueryBook>().AS("Book").ToList();
                var query_as_ = db.Queryable<Book>().Select<QueryBook>().ToList();

                //8、 where 条件查询
                //使用 lambda 表达式
                var query_where_lambda = db.Queryable<Book>().Where(x => x.IsDelete == 0 && x.Author == "刘慈欣").ToList();

                //使用sql语句 和 SqlParameter 参数
                var query_where_param = db.Queryable<Book>().Where("isdelete=0 and author=@author", new { author = "刘慈欣" }).ToList();

                //9、模糊查询
                var query_like = db.Queryable<Book>().Where(x => x.Title.Contains("图书")).ToList();

                //10、in
                string[] autors = new string[] { "刘慈欣", "罗贯中" };
                var query_in = db.Queryable<Book>().Where(x => autors.Contains(x.Author)).ToList();

                //11、OrderBy 排序
                //等同于sql：select top 10 * from Book order by inprice desc,sn asc
                var query_order1 = db.Queryable<Book>().OrderBy(x => x.InPrice, OrderByType.Desc).OrderBy(x => x.Sn, OrderByType.Asc).Take(10).ToList();
                var query_order2 = db.Queryable<Book>().OrderBy(" inprice desc,sn asc ").Take(10).ToList();
            }
        }


        /// <summary>
        /// 基础查询进阶
        /// </summary>
        public static void NormalQueryOther()
        {
            using (var db = SqlSugarHelper.GetDB())
            {
                //1、SUM求某列的和
                var fun_sum = db.Queryable<Book>().Sum(x => x.InPrice);

                //2、判断是否存在
                var fun_any = db.Queryable<Book>().Any(x => x.Title == "三体");

                //3、* 分页查询 *
                int pageIndex = 1;//页码
                int pageSize = 10;//每页数据条数
                                  //同步分页
                int count = 0;//总数
                var query_page = db.Queryable<Book>().ToPageList(pageIndex, pageSize, ref count);
                //异步分页
                RefAsync<int> count_async = 0;//总数
                var query_page_async = db.Queryable<Book>().ToPageListAsync(pageIndex, pageSize, count_async).Result;

                //4、去重查询
                var query_distinct = db.Queryable<Book>().Distinct().Select(x => x.Author).ToList();

                //5、并集查询，需要自定义 并集查询结果 的实体类
                var query_union1 = db.Queryable<Book>().Select(x => new Union_UserModel { UserId = x.CreateUserId, UserName = x.Author });
                var query_union2 = db.Queryable<User>().Select(x => new Union_UserModel { UserId = x.Id, UserName = x.Name });
                //union(并集，对结果去重)
                var query_union = db.Union<Union_UserModel>(query_union1, query_union2).ToList();
                //union all(并集不去重)
                var query_unionAll = db.UnionAll<Union_UserModel>(query_union1, query_union2).ToList();

                //6、没有实体的查询 dynamic
                var query_noModel = db.Queryable<dynamic>("b").AS("Book").Select("b.id,b.sn,b.title,b.author").Where("Sn=@Sn", new { Sn = 1 }).ToList();

                //7、二次查询 MergeTable()
                //将进价乘以2，再分组查询
                var query_child_author = db.Queryable<Book>().Select(x => new
                {
                    Sn = x.Sn,
                    Id = x.Id,
                    InPrice = x.InPrice * 2,
                    Title = x.Title,
                    Author = x.Author
                }).MergeTable()//将查询结果转为一个表，后面二次查询
                .GroupBy(x => x.Author)
                .Select(x => new { 作者 = x.Author, 平均进价 = SqlFunc.AggregateAvg(x.InPrice) })
                .ToList();

                //按日期生成新表，再分组查询
                var query_child_date = db.Queryable<Book>().Select(x => new
                {
                    Sn = x.Sn,
                    Id = x.Id,
                    Title = x.Title,
                    InPrice = x.InPrice,
                    CreateDate = x.CreateTime.Date.ToString()
                }).MergeTable()//将查询结果转为一个表，后面二次查询
                .GroupBy(x => x.CreateDate)
                .Select(x => new { 创建日期 = x.CreateDate, 平均进价 = SqlFunc.AggregateAvg(x.InPrice) })
                .ToList();

                
            }
        }
    }
}
